﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace _009_SqlLite
{
    public static class SQLiteHelper
    {
        /// <summary>
        /// 数据库介质路径
        /// </summary>
        private static string _dbFilePath = AppDomain.CurrentDomain.BaseDirectory + "DB.db";

        /// <summary>
        /// 连接句柄
        /// </summary>
        private static SQLiteConnection _sqlCon;

        /// <summary>
        /// 创建数据库
        /// </summary>
        public static void CreateDb()
        {
            //创建数据库文件
            if (!File.Exists(_dbFilePath))
            {
                SQLiteConnection.CreateFile(_dbFilePath);
            }
        }
        /// <summary>
        /// 获取连接
        /// </summary>
        /// <returns></returns>
        public static SQLiteConnection GetCondition()
        {
            try
            {
                if (_sqlCon == null)
                {
                    //连接数据库
                    SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder();
                    sb.DataSource = _dbFilePath;
                    _sqlCon = new SQLiteConnection();
                    _sqlCon.ConnectionString = sb.ConnectionString;
                    _sqlCon.Open();
                }
                return _sqlCon;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// Sql执行
        /// </summary>
        /// <param name="sql"></param>
        public static int DealSql(string sql)
        {
            try
            {
                SQLiteCommand cmd = _sqlCon.CreateCommand();
                cmd.CommandText = sql;
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
                return 0;
            }
        }

        /// <summary>
        /// Sql查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable SelectSql(string sql)
        {
            try
            {
                SQLiteCommand cmd = _sqlCon.CreateCommand();
                cmd.CommandText = sql;
                SQLiteDataAdapter dr = new SQLiteDataAdapter(cmd);
                DataTable dt = new DataTable();
                dr.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
                return new DataTable();
            }
        }

    }
}
